<?php

require_once("functions.inc.php");
require_once("database.inc.php");
require_once("track.inc.php");
require_once("../HT_Client/HT_Client.class.php");

$dbm = connect_db();
$client = new HT_Client();

ini_set("memory_limit","100M");
$debug = (!hit() && isset($_GET['debug']));
if (isset($_GET['allianceid']) && is_numeric($_GET['allianceid']) && $_GET['allianceid'] > 0) { //get fed overview
	$allianceid = $_GET['allianceid'];
	if (!$query = mysql_query("SELECT COUNT(*) num FROM fed_members WHERE allianceid = $allianceid", $dbm)) die (mysql_error());
	$row = mysql_fetch_array($query);
	if ($row['num'] > 0) {
		$federations = getAsocArr("fed_details", "id", "name", $dbm);
		$fedfetched = getAsocArr("fed_details", "id", "fetched", $dbm);
?>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Federation member overview for <?php echo $federations[$allianceid]; ?></title>
</head>
<body>
<a href="fedimport.php">Back to list of feds</a><br /><br />
If you want to update the memberlist (last update: <?php echo $fedfetched[$allianceid]; ?>), use this form: <br /><br />
<form method="post" action="fedimport.php">
<input name="login" size="20"> Login<br />
<input name="seccode" size="20" type="password"> Security code<br />
<input name="allianceid" size="6" value="<?php echo $allianceid; ?>"> AllianceID<br />
<input type="submit" value="Go!">
</form>
<hr>
Watch the <a href="hallofcool.php?allianceid=<?php echo $allianceid; ?>">Hall of Cool</a> for <?php echo $federations[$allianceid]; ?> members uploaded so far
<hr>
<?php
		if (!$query = mysql_query("SELECT *
			FROM fed_members m, flags_updated u
			WHERE m.userid = u.userid
			AND allianceid = $allianceid
			ORDER BY FetchedDate ASC
			LIMIT 100
		", $dbm)) die (mysql_error());
		if (mysql_num_rows($query) > 0) {
			echo "<a name=\"longtime\"></a>";
			echo "Members of {$federations[$allianceid]} who have not recently updated their coolness (100 least up-to-date max.)<br />\n";
			echo "(<a href=\"fedimport.php?allianceid=$allianceid#never\">scroll down</a> to never-updated members)<br />\n";
			echo "<table>\n";
			echo "<tr><th>User<th>Team<th>Last update<th>\n";
			while ($row = mysql_fetch_array($query)) {
				echo "<tr><td>{$row['LoginName']}<td><img src=\"flags/{$row['LeagueID']}flag.gif\" border=\"1\"> {$row['TeamName']}<td>{$row['FetchedDate']}<td><a href=\"coolness.php?tid={$row['TeamID']}#submit\">update</a>\n";
			}
			echo "</table><br /><br />\n";
		}

		if (!$query = mysql_query("SELECT *
			FROM fed_members m LEFT JOIN flags_updated u ON m.userid = u.userid
			WHERE allianceid = $allianceid
			AND u.userid IS NULL
			ORDER BY membersince ASC
			LIMIT 100
		", $dbm)) die (mysql_error());
		if (mysql_num_rows($query) > 0) {
			echo "<hr>\n";
			echo "<a name=\"never\"></a>";
			echo "Members who have never uploaded their coolness (100 oldest members max.)<br />\n";
			$subquery = mysql_query("SELECT DISTINCT m.userid, m.allianceid, membersince FROM `fed_members` m left join flags_updated u on m.userid = u.userid where u.userid is null order by membersince asc", $dbm) or die (mysql_error());
			$subnrows = mysql_num_rows($subquery);
			$hoursleft = ceil($subnrows / 250);
			$subrow = mysql_fetch_array($subquery);
			echo "Of all the federations uploaded to the Hall of Cool, 250 non-uploaded teams are automatically uploaded every hour. This is done in order of membership, oldest first. Currently, the oldest team that is a member of a federation, but not yet uploaded, has been a member since <b>{$subrow['membersince']}</b><!-- userid: {$subrow['userid']} -->, and there is a total of $subnrows teams in the queue. In about $hoursleft hours, the fed will be entirely uploaded.<br /><br />\n";
			echo "If this list contains ownerless teams or non-members, update the fed using the form at the top of the page!<br />\n";
			echo "<a href=\"fedimport.php?allianceid=$allianceid#longtime\">(back to top)</a><br />\n";
			echo "<table>\n";
			echo "<tr><th>User<th>Team<th>Member of fed since<th>\n";
			while ($row = mysql_fetch_array($query)) {
				$lastupdate = getLastUpdate();
				if (!$subquery = mysql_query("SELECT * FROM `teams_$lastupdate` WHERE userid = {$row['userid']}")) die (mysql_error());
				if (mysql_num_rows($subquery) > 0) {
					$subrow = mysql_fetch_array($subquery);
					echo "<tr><td>{$subrow['LoginName']}<td><img src=\"flags/{$subrow['LeagueID']}flag.gif\" border=\"1\"> {$subrow['TeamName']}<td>{$row['membersince']}<td><a href=\"coolness.php?tid={$subrow['TeamID']}#submit\">upload</a>\n";
				} else {
					echo "<tr><td>New user ({$row['userid']})<td>Unknown<td>{$row['membersince']}<td><a href=\"http://www.hattrick.org/Common/TeamDetails.asp?UserID={$row['userid']}\">Look up TeamID on Hattrick (click 2x)</a>\n";
				}
			}
		}
	} else {
		echo "No members are registered for this fed! Please <a href=\"fedimport.php\">re-import it</a>.<br />\n";
	}
?>
</table>
<?php urchin(); ?>
</body>
<?php
} else if (!isset($_POST['login']) || !isset($_POST['seccode'])) { //show login form and list of feds
?>
<html>
<head>
<title>Federation Import</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<style>
td.fresh {
	font-weight: bold;
}
td.allok {
        background-color: #AAFFAA;
}

td.partok {
        background-color: #FFFFAA;
}

td.notok {
        background-color: #FFAAAA;
}
</style>
</head>
<body>
Please enter your username and security code (not password!) below, as well as the ID of the federation you want to update.
<form method="post" action="fedimport.php">
<input name="login" size="20"> Login<br />
<input name="seccode" size="20" type="password"> Security code<br />
<input name="allianceid" size="6"> AllianceID<br />
<input type="submit" value="Go!">
</form>
<b>If you want to submit your coolness, go to the <a href="coolness.php#submit">Coolness calculator</a>.<br />
If you want to add all members from your fed, click "Manage members" for your fed in the list below.<br />
If your fed is not in the list, use the form above.</b><br />
<br /><br />
<hr>
<br /><br /><br />
<hr>
<br /><br />
<table>
<tr><th colspan="3">Legend:
<tr><th>Members<th>Updated/watched
<tr><td class="allok">All uploaded<td class="allok">This week <b>(today)</b>
<tr><td class="partok">Half uploaded<td class="partok">More than 1 wk ago<br />Removed from HoC dropdown
<tr><td class="notok">None uploaded/<br />Less than 5 in all<td class="notok">More than 2 wk ago<br />DELETED after 3 wk!
</table><br />If a federation is not updated or looked up at the Hall of Cool for more than 3 weeks, the fed will be removed from the database. Of course, it can always be reimported manually.<br /><br />
<?php
$numhocmembers = array();

if (!$query = mysql_query("SELECT allianceid, COUNT(*) num FROM fed_members LEFT JOIN flags_updated ON fed_members.userid = flags_updated.userid WHERE flags_updated.userid IS NOT NULL GROUP BY allianceid", $dbm)) die (mysql_error());
while ($row = mysql_fetch_array($query)) {
	$numhocmembers[$row['allianceid']] = $row['num'];
}

if (!$query = mysql_query("SELECT *, UNIX_TIMESTAMP() - UNIX_TIMESTAMP(fetched) diffu, UNIX_TIMESTAMP() - UNIX_TIMESTAMP(latest) diffl, agent FROM fed_details, fed_log WHERE id = allianceid ORDER BY LTRIM(name) ASC", $dbm)) die (mysql_error());
echo "<table>\n";
echo "<tr><th>ID<th>Federation name<th>Members in HoC<th>Last updated<th>Last seen</th>\n";
while ($row = mysql_fetch_array($query)) {
	if (!isset($numhocmembers[$row['id']])) $numhocmembers[$row['id']] = 0;
	if ($numhocmembers[$row['id']] < 5) {
		$hoccolor = "FFAAAA";
	} else {
		$hocratio = $numhocmembers[$row['id']] / $row['nummembers'];
		$hoccolor = "";
		echo "<!-- $hocratio --!>";
		if ($hocratio <= 0.5) {
			$hoccolor = sprintf("%02X%02X%02X", 255, 170 + round($hocratio*170), 170);
		} else {
			$hoccolor = sprintf("%02X%02X%02X", 170 + round((1-$hocratio)*170), 255, 170);
		}
		if ($numhocmembers[$row['id']] == $row['nummembers']) $hocclass = "allok";
		if ($numhocmembers[$row['id']] < 100 && ($numhocmembers[$row['id']] / $row['nummembers'] < 0.2)) $hocclass = "notok";
	}
	$hocstyle = "background-color: #$hoccolor;";
	$updateclass = "allok";
	$latestclass = "allok";
	if ($row['diffu'] <= 86400) $updateclass = "allok fresh";
	if ($row['diffl'] <= 86400) $latestclass = "allok fresh";
	if ($row['diffu'] > 7*86400) $updateclass = "partok";
	if ($row['diffl'] > 7*86400) $latestclass = "partok";
	if ($row['diffu'] > 14*86400) $updateclass = "notok";
	if ($row['diffl'] > 14*86400) $latestclass = "notok";
	echo "<tr><td>{$row['id']}<td>{$row['name']}";
	if ($row['abbrev'] != '') echo " ({$row['abbrev']})";
	echo "<td style=\"$hocstyle\">{$numhocmembers[$row['id']]} / {$row['nummembers']}<td class=\"$updateclass\">{$row['fetched']}<td class=\"$latestclass\">{$row['latest']}<td><a href=\"hallofcool.php?allianceid={$row['id']}\">Hall&nbsp;of&nbsp;Cool</a> | <a href=\"fedimport.php?allianceid={$row['id']}\">Manage/update</a>";
	if ($debug) echo " | <a href=\"revert.php?allianceid={$row['id']}\">{$row['agent']}</a>";
	echo "\n";
}
echo "</table>\n";
urchin();
?>
</body>
</html>
<?php
} else { //update the fed
	if (isset($_POST['allianceid']) && is_numeric($_POST['allianceid']) && $_POST['allianceid'] > 0) {
		$allianceid = $_POST['allianceid'];
	} else {
		die ('invalid AllianceID provided!');
	}
	
	if (!$client->Login($_POST['login'],$_POST['seccode'],"Maptrick Fed Import")) die ("Aborting, try again later\n");

	echo "importing federation roles...<br />\n";
	$roles = $client->getAllianceRoles($allianceid);

	if ($roles) {
		foreach ($roles as $role) {
			$name = mysql_real_escape_string($role['name']);
			if (!mysql_query("REPLACE INTO fed_roles VALUES({$role['id']}, $allianceid, '$name', {$role['rank']})", $dbm)) die (mysql_error());
		}
	} else {
		die("Fetching of federation roles for fed $allianceid failed! Are you a member?\n");
	}

	echo "importing federation details...<br />\n";
	$details = $client->getAllianceDetails($allianceid);

	if ($details) {
		if ($details === "remove") {
			if (!mysql_query("DELETE FROM fed_details WHERE id = $allianceid", $dbm)) die (mysql_error());
			if (!mysql_query("DELETE FROM fed_members WHERE allianceid = $allianceid", $dbm)) die (mysql_error());
			if (!mysql_query("DELETE FROM fed_roles WHERE allianceid = $allianceid", $dbm)) die (mysql_error());
			if (!mysql_query("DELETE FROM fed_log WHERE allianceid = $allianceid", $dbm)) die (mysql_error());
			die("That fed does not exist... if it was in the database, it is now deleted.\n");
		} else {
			$name = mysql_real_escape_string($details['name']);
			$host = getenv("REMOTE_ADDR");
			if (!mysql_query("REPLACE INTO fed_log VALUES({$details['id']}, NOW(), '$host', 'updated')", $dbm)) die (mysql_error());
			$abbrev = "NULL";
			$logourl = "NULL";
			if (isset($details['abbrev'])) $abbrev = "'" . mysql_real_escape_string($details['abbrev']) . "'";
			if (isset($details['logourl'])) $logourl = "'" . mysql_real_escape_string($details['logourl']) . "'";
			mysql_query("REPLACE INTO fed_details VALUES({$details['id']}, '$name', {$details['nummembers']}, $abbrev, $logourl, NOW())", $dbm) or die (mysql_error());
		}
	} else {
		die("Fetching of details for fed $allianceid failed! Are you sure the ID is $allianceid? Or is Hattrick down?\n");
	}

	echo "importing federation members...<br />\n";
	$members = $client->getAllianceMembers($allianceid);

	if ($members) {
		$fedroles = getAsocArr("fed_roles", "id", "name", $dbm);
		$oldmembers = array();
		if (!$query = mysql_query("SELECT * FROM fed_members WHERE allianceid = $allianceid", $dbm)) die (mysql_error());
		while ($row = mysql_fetch_array($query)) {
			$oldmember = array();
			$oldmember['roleid'] = $row['roleid'];
			$oldmember['member'] = $row['membersince'];
			$oldmember['userid'] = $row['userid'];
			$oldmembers[$row['userid']] = $oldmember;
		}
		if ($query = !mysql_query("DELETE FROM fed_members WHERE allianceid = $allianceid", $dbm)) die (mysql_error());
		$checked = array();
		foreach ($members as $member) {
			if (!mysql_query("INSERT INTO fed_members VALUES ({$member['userid']},$allianceid,{$member['roleid']},'{$member['member']}')",$dbm)) die (mysql_error());
			if (!isset($oldmembers[$member['userid']])) {
				echo "user {$member['userid']} joined as {$fedroles[$member['roleid']]} on {$member['member']}<br />\n";
				$checked[$member['userid']] = true;
			} else if ($oldmembers[$member['userid']]['roleid'] != $member['roleid']) {
				echo "user {$member['userid']} changed role from {$fedroles[$oldmembers[$member['userid']]['roleid']]} to {$fedroles[$member['roleid']]}<br />\n";
				$checked[$member['userid']] = true;
			} else if ($oldmembers[$member['userid']]['member'] != $member['member']) {
				echo "user {$member['userid']} left the fed but rejoined in the same role on {$member['member']}<br />\n";
				$checked[$member['userid']] = true;
			} else {
				$checked[$member['userid']] = true; //user did not change at all
			}
		}
		foreach ($oldmembers as $oldmember) {
			if (!isset($checked[$oldmember['userid']])) echo "user {$oldmember['userid']}, member since {$oldmember['member']}, left the federation<br />\n";
		}
	} else die("Fetching of members for fed $allianceid failed! Hattrick probably timed out; try again at a later time.\n");

	echo "Update of {$details['name']} ($allianceid) was successful.<br />\n";
	echo "<a href=\"hallofcool.php?allianceid=$allianceid\">Show the Hall of Cool for this fed</a>, or <a href=\"fedimport.php?allianceid=$allianceid\">get a list of teams from this fed</a> that have not (recently) used the Coolness calculator.<br />\n";
}

?>
